 using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Security.Cryptography;
using System.IO;

namespace botliga
{
    public enum resultado
    {
        sentinel,
        scourge,
        none,
        draw
    }
    public class Admin
    {
        public int idAdmin
        { get; set; }
        public string Nombre
        { get; set; }
        public int Access
        { get; set; }
        public Admin(int nidAdmin, string nNombre, int nAccess)
        {
            idAdmin = nidAdmin;
            Nombre = nNombre;
            Access = nAccess;
        }
    }
    public class Bans
    {
        public int idBans
        { get; set; }
        public string Nombre
        { get; set; }
        public DateTime FechaExpiracion
        { get; set; }
        public Admin _admin
        {
            get;
            set;
        }
        public Bans(int nidBans, string nNombre, DateTime nFechaExpiracion, Admin n_admin)
        {
            idBans = nidBans;
            Nombre = nNombre;
            FechaExpiracion = nFechaExpiracion;
            _admin = n_admin;
        }
    }
    public class Vouch
    {
        public int idVouch
        { get; set; }
        public string Nombre
        { get; set; }
        public Admin Voucher
        { get; set; }
        public Vouch(int nidVouch, string nNombre, Admin nVoucher)
        {
            idVouch = nidVouch;
            Nombre = nNombre;
            Voucher = nVoucher;
        }
    }
    public class Game
    {
        private DateTime dateTime;

        public int idGame
        { get; set;  }
        public DateTime Fecha
        {
            get;
            set;
        }
        public resultado _resAcambiar
        { get; set; }
        public resultado _resultado
        { get; set; }
        public Game(int nidGame, DateTime nFecha, resultado n_resultado)
        {
            idGame = nidGame;
            Fecha = nFecha;
            _resultado = n_resultado;
        }

        public Game(DateTime dateTime)
        {
            this.dateTime = dateTime;
            this._resAcambiar = resultado.none;
            this._resultado = resultado.none;
            this.idGame = -1;
            
        }
    }
    public class Vouch_plays_Game
    {
        Game idGame;
        Vouch Player;
        int team;
        public Vouch_plays_Game(Game nidGame, Vouch nPlayer, int nteam)
        {
            idGame = nidGame;
            Player = nPlayer;
            team = nteam;
        }
    }
    public sealed class DBconnection : IDisposable
    {
        private static volatile DBconnection instance;
        private static object syncRoot = new Object();
        MySqlConnection connection;
        MySqlDataAdapter adapter;

        private DBconnection() {
            //Initialize mysql connection

            StreamReader sr = new StreamReader(Directory.GetCurrentDirectory() + "\\mysqlcon.txt");
            connection = new MySqlConnection(sr.ReadLine());
            
        }
        public bool isRootAdmin(string user)
        {
            user = user.ToLower();
            user = "\"" + user + "\"";
            string query = "select * from Admin where Access = 0 AND Nombre = " + user;
            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query, connection);
            DataSet algo = new DataSet();
            if (adapter.Fill(algo) == 1)
                return true;
            else
                return false;
        }
        public bool isAdmin(string user)
        {
            user = user.ToLower();
            user = "\"" + user + "\"";
            string query = "select * from Admin where Access = 1 AND Nombre = " + user;
            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query, connection);
            DataSet algo = new DataSet();
            if (adapter.Fill(algo) == 1) 
                return true;
            else
                return false;
        }
        public bool isVoucher(string user)
        {
            user = user.ToLower();
            user = "\"" + user + "\"";
            string query = "select * from Admin where Access = 2 AND Nombre = " + user;
            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query, connection);
            DataSet algo = new DataSet();
            if (adapter.Fill(algo) == 1)
                return true;
            else
                return false;
        }
        public bool isCapitan(string user)
        {
            user = user.ToLower();
            user = "\"" + user + "\"";
            string query = "select * from Admin where Access = 3 AND Nombre = " + user;
            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query, connection);
            DataSet algo = new DataSet();
            if (adapter.Fill(algo) == 1)
                return true;
            else
                return false;
        }
        internal bool isHost(string user)
        {
            user = user.ToLower();
            user = "\"" + user + "\"";
            string query = "select * from Admin where Access = 4 AND Nombre = " + user;
            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query, connection);
            DataSet algo = new DataSet();
            if (adapter.Fill(algo) == 1)
                return true;
            else
                return false;
        }
        public bool isVouched(string user)
        {
            user = user.ToLower();
            user = "\"" + user + "\"";
            string query = "select * from Vouch where Inactivo='0' AND Nombre = " + user;
            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query, connection);
            DataSet algo = new DataSet();
            if (adapter.Fill(algo) == 1)
                return true;
            else
                return false;
        }
        public bool wasVouched(string user)
        {
            user = user.ToLower();
            user = "\"" + user + "\"";
            string query = "select * from Vouch where Inactivo='1' AND Nombre = " + user;
            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query, connection);
            DataSet algo = new DataSet();
            if (adapter.Fill(algo) == 1)
                return true;
            else
                return false;
        }
        public bool addAdmin(string user,int access)
        {
            string insertSQL = "INSERT INTO Admin (Nombre, Access) VALUES ('" + user + "', '"
                + access + "')";
            MySqlCommand cmd = new MySqlCommand(insertSQL, connection);
            int executed = 0;
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
                executed = cmd.ExecuteNonQuery();
                connection.Close();
            }
            if (executed == 1)
                return true;
            else
                return false;
        }
        public bool addVouch(string user, string voucher,string link)
        {
            string formatForMySql = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string insertSQL = "INSERT INTO Vouch (Nombre, Voucher,Link,Fecha) VALUES ('" + user + "', (select idAdmin from admin where Nombre = '" + voucher + "'),'" + link + "','" + formatForMySql + "')";
            MySqlCommand cmd = new MySqlCommand(insertSQL, connection);

            int executed = 0;
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
                executed = cmd.ExecuteNonQuery();
                connection.Close();
            }
            if (executed == 1)
                return true;
            else
                return false;
        }
        public bool reVouch(string user,int admin,string link)
        {
            string formatForMySql = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string insertSQL = "UPDATE Vouch SET Fecha='" + formatForMySql + "',Inactivo=0, Link='" + link + "', AdminUV=" + admin;
            MySqlCommand cmd = new MySqlCommand(insertSQL, connection);

            int executed = 0;
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
                executed = cmd.ExecuteNonQuery();
                connection.Close();
            }
            if (executed == 1)
                return true;
            else
                return false;
        }
        public static DBconnection Instance
        {
            get
            {
                if (instance == null)
                {
                    lock (syncRoot)
                    {
                        if (instance == null)
                            instance = new DBconnection();
                    }
                }

                return instance;
            }
        }

        public int addGame(GameStart game)
        {
            string formatForMySql = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string insertSQL = "INSERT INTO game (Fecha) VALUES ('" + formatForMySql + "')";
            MySqlCommand cmd = new MySqlCommand(insertSQL, connection);
            
            int executed = 0;
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
                executed = cmd.ExecuteNonQuery();
            if(connection.State == ConnectionState.Open)
            {
                connection.Close();
            }
            int gid = (int)cmd.LastInsertedId;
            if (executed == 1)
                return gid;
            else
                return -1;
        }
        public bool promote(string User)
        {
            string formatForMySql = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string insertSQL = "UPDATE admin SET Fecha='"+formatForMySql+"', Access = Access - 1 where Nombre = '"+User+"'";
            MySqlCommand cmd = new MySqlCommand(insertSQL, connection);
            int executed = 0;
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            executed = cmd.ExecuteNonQuery();
            if (connection.State == ConnectionState.Open)
            {
                connection.Close();
            }

            if (executed == 0)
                return false;
            else
                return true;
        }
        public bool demote(string User)
        {

            string formatForMySql = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string insertSQL = "UPDATE admin SET Fecha='"+formatForMySql+"', Access = Access + 1 where Nombre = '" + User + "'";
            MySqlCommand cmd = new MySqlCommand(insertSQL, connection);
            int executed = 0;
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            executed = cmd.ExecuteNonQuery();
            if (connection.State == ConnectionState.Open)
            {
                connection.Close();
            }

            if (executed == 0)
                return false;
            else
                return true;
        }
        public bool transfer(string User,string newName)
        {
            string insertSQL = "UPDATE vouch SET Nombre = '" + newName + "' where Nombre = '" + User + "'";
            string insertSQL2 = "UPDATE admin SET Nombre = '" + newName + "' where Nombre = '" + User + "'";
            string insertSQL3 = "UPDATE bans SET Nombre = '" + newName + "' where Nombre = '" + User + "'";
                        
            MySqlCommand cmd = new MySqlCommand(insertSQL, connection);
            MySqlCommand cmd1 = new MySqlCommand(insertSQL2, connection);
            MySqlCommand cmd2 = new MySqlCommand(insertSQL3, connection);
            int executed = 0;
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            executed = cmd.ExecuteNonQuery();
            executed = cmd1.ExecuteNonQuery();
            executed = cmd2.ExecuteNonQuery();
            if (connection.State == ConnectionState.Open)
            {
                connection.Close();
            }

            if (executed == 0)
                return false;
            else
                return true;
        }
        public bool deleteadmin(string User)
        {
            string insertSQL = "DELETE from admin where Nombre = '" + User + "'";
            MySqlCommand cmd = new MySqlCommand(insertSQL, connection);
            int executed = 0;
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            executed = cmd.ExecuteNonQuery();
            if (connection.State == ConnectionState.Open)
            {
                connection.Close();
            }

            if (executed == 0)
                return false;
            else
                return true;
        }
        /// <summary>
        /// Define el resultado de un juego.
        /// </summary>
        /// <param name="game">Juego en cuestión</param>
        /// <returns>Los resultados del juego(Sentinel+Scourge,Draw+null,Base de datos caida + null)</returns>
        public string[] ResultGame(GameStart game)
        {

            string insertSQL = "UPDATE game SET Resultado = '" + game.GetGame()._resultado.ToString() + "' WHERE idGame ='" + game.GetGame().idGame + "'";
            MySqlCommand cmd = new MySqlCommand(insertSQL, connection);
            int executed = 0;
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            executed = cmd.ExecuteNonQuery();
            if (connection.State == ConnectionState.Open)
            {
                connection.Close();
            }

            if (executed == 0)
                return new string[] { "Base de datos caida", null };
            else
            {
                int teamWinner;
                string[] player_name = new string[10];
                double[] player_ratings = new double[10];
                int[] player_teams = new int[10];
                double[] team_ratings = new double[2];
                double[] team_winners = new double[2];
                int[] team_numplayers = new int[2];

                int num_players = 0;
                int i = 0;
                team_ratings[0] = 0.0;
                team_ratings[1] = 0.0;
                team_numplayers[0] = 0;
                team_numplayers[1] = 0;
                if (game.GetGame()._resultado == resultado.draw)
                {
                    teamWinner = -1;
                    foreach (var x in game._Traunted)
                    {
                        Traunt(x, game);
                        Ban(x, 13, GetTraunts(x), "TRUANT in game: " + game.GetGame().idGame);
                        Warn(x, 13, -CurrentHP(x), "TRUANT in game: " + game.GetGame().idGame);
                    }
                    return new string[] { "La partida "+game.GetGame().idGame+" ha sido declarada como empate.", null };
                }
                else if (game.GetGame()._resultado == resultado.sentinel)
                {
                    teamWinner = 0;
                    team_winners[0] = 1.0;
                    team_winners[1] = 0.0;
                }
                else
                {
                    teamWinner = 1;
                    team_winners[0] = 0.0;
                    team_winners[1] = 1.0;
                }
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }
                foreach (var x in game._Players)
                {

                    if (!game._Excluded.Contains(x))
                    {
                        var User = x.ToLower();
                        var us = User;
                        User = "\"" + User + "\"";
                        string query = "(select * from Vouch where Nombre = " + User + ")";
                        adapter = new MySqlDataAdapter(query, connection);
                        DataSet algo = new DataSet();
                        int l = adapter.Fill(algo);
                        player_name[num_players] = us;
                        player_ratings[num_players] = (double)algo.Tables[0].Rows[0]["Exp"] + ((int)algo.Tables[0].Rows[0]["Streak"]) * 2;
                        if (i < 5)
                        {
                            player_teams[num_players] = 0;
                            team_ratings[0] += player_ratings[num_players];
                            team_numplayers[0]++;
                        }
                        else
                        {
                            player_teams[num_players] = 1;
                            team_ratings[1] += player_ratings[num_players];
                            team_numplayers[1]++;
                        }
                        //prepare adapter to run query
                        // sentinel 1 2 3 4           5  , scourge 6 7 8 9 10
                        // sentinel a b c e[excluded] d  , scourge f g h i j
                        num_players++;
                        i++;
                    }
                    else
                    {
                        i++;//10
                    }
                    
                }
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
                double min = 0;
                double[] player_ratingsOld = new double[10];
                player_ratings.CopyTo(player_ratingsOld, 0);
                Elo _elo = new Elo(player_name, num_players, ref player_ratings, player_teams, team_ratings, team_winners, team_numplayers);
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }
                for (int kk = 0; kk < num_players; kk++)
                {
                    string insertSQL2;
                    if ((player_ratingsOld[kk] - player_ratings[kk]) < 0 && (player_ratingsOld[kk] - player_ratings[kk]) < min)
                    {
                        min = (player_ratingsOld[kk] - player_ratings[kk]);
                    }
                    if (player_teams[kk] == teamWinner)
                    {
                        insertSQL2 = "UPDATE vouch SET Confianza = Confianza + '0.3' , Streak = Streak + 1 , Exp = '" + player_ratings[kk].ToString().Replace(',', '.') + "'  WHERE Nombre ='" + player_name[kk] + "'";
                    }
                    else
                    {
                        insertSQL2 = "UPDATE vouch SET Confianza = Confianza + '0.3' , Streak = 0 , Exp = '" + player_ratings[kk].ToString().Replace(',', '.') + "'  WHERE Nombre ='" + player_name[kk] + "'";
                    }
                    cmd = new MySqlCommand(insertSQL2, connection);
                    cmd.ExecuteNonQuery();
                    insertSQL2 = "UPDATE vouch_plays_game SET Streak =  (select Streak from vouch where Nombre = '" + player_name[kk] + "'),Result ='" + (player_ratings[kk] - player_ratingsOld[kk]).ToString().Replace(',', '.') + "' where Game_idGame = '" + game.GetGame().idGame + "' AND Vouch_idvouch = (select idvouch from vouch where Nombre = '" + player_name[kk] + "')";
                    cmd = new MySqlCommand(insertSQL2, connection);
                    cmd.ExecuteNonQuery();
                }
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }

                min *= 2;
                foreach (var x in game._Excluded)
                {
                    Exclude(x, game);
                    double ratinggg = Math.Round(min, 2);
                    if (team_numplayers[0] < 5)
                    {
                        _elo._resultado[0] = _elo._resultado[0] + "[Excluded|" + x + "] " + ratinggg + " ";
                    }
                    else
                    {
                        _elo._resultado[1] = _elo._resultado[1] + "[Excluded|" + x + "] " + ratinggg + " ";
                    }
                    if (connection.State == ConnectionState.Closed)
                        connection.Open();
                    string insertSQL2;
                    insertSQL2 = "UPDATE vouch SET Confianza = Confianza - '5', Streak = 0 , Exp = Exp + '" + min.ToString().Replace(',', '.') + "'  WHERE Nombre ='" + x + "'";
                    cmd = new MySqlCommand(insertSQL2, connection);
                    cmd.ExecuteNonQuery();
                    insertSQL2 = "UPDATE vouch_plays_game SET Streak =  (select Streak from vouch where Nombre = '" + x + "'),Result ='" + ratinggg.ToString().Replace(',', '.') + "' where Game_idGame = '" + game.GetGame().idGame + "' AND Vouch_idvouch = (select idvouch from vouch where Nombre = '" + x + "')";
                    cmd = new MySqlCommand(insertSQL2, connection);
                    cmd.ExecuteNonQuery();
                    if (connection.State == ConnectionState.Open)
                        connection.Close();
                }


                if (executed == 1)
                    return _elo._resultado;
                else
                    return new string[] { "Base de datos caída", null };
            }

        }

        private int GetTraunts(string x)
        {
            x = x.ToLower();
            x = "\"" + x + "\"";
            string query = "select Traunt from vouch where Nombre = " + x;
            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query, connection);
            DataSet algo = new DataSet();
            if (adapter.Fill(algo) == 1)
                return (int)algo.Tables[0].Rows[0][0];
            else
                return -1;
        }

        private bool Exclude(string x, GameStart game)
        {
            string insertSQL;
            if (connection.State == ConnectionState.Closed)
                connection.Open();
            int executed = 0;
            insertSQL = "INSERT INTO vouch_has_exclude (vouch_idvouch,game_idGame) VALUES ((select idvouch from vouch where Nombre ='" + x + "'),'" + game.GetGame().idGame + "')";
            MySqlCommand cmd = new MySqlCommand(insertSQL, connection);
            executed = cmd.ExecuteNonQuery();

            List<string> Excluders = game.getExcluders(x);
            foreach (var ex in Excluders)
            {
                insertSQL = "INSERT INTO vouch_excluder (excluder, excluded,excluded_game_idGame) VALUES ((select idvouch from vouch where Nombre ='" + ex + "'),(select idvouch from vouch where Nombre ='" + x + "'),'" + game.GetGame().idGame + "')";
                cmd = new MySqlCommand(insertSQL, connection);
                executed = cmd.ExecuteNonQuery();
            }
            if (connection.State == ConnectionState.Open)
                connection.Close();

            if (executed == 1)
                return true;
            else
                return false;

        }

        private bool Traunt(string x, GameStart game)
        {
             
             string insertSQL;
             if (connection.State == ConnectionState.Closed)
                 connection.Open();
             int executed = 0;
             insertSQL = "INSERT INTO vouch_has_traunt (vouch_idvouch,game_idGame) VALUES ((select idvouch from vouch where Nombre ='" + x + "'),'" + game.GetGame().idGame + "')";
             MySqlCommand cmd = new MySqlCommand(insertSQL, connection);
             executed = cmd.ExecuteNonQuery();

             insertSQL = "UPDATE vouch SET Traunt = Traunt + 1,Confianza = Confianza - 10 WHERE Nombre ='" + x + "'";
             cmd = new MySqlCommand(insertSQL, connection);
             executed = cmd.ExecuteNonQuery();

             List<string> Traunters = game.getTraunters(x);
             foreach (var ex in Traunters)
             {
                 insertSQL = "INSERT INTO vouch_traunter (traunter, traunted,traunted_game_idGame) VALUES ((select idvouch from vouch where Nombre ='" + ex + "'),(select idvouch from vouch where Nombre ='" + x + "'),'" + game.GetGame().idGame + "')";
                 cmd = new MySqlCommand(insertSQL, connection);
                 executed = cmd.ExecuteNonQuery();
             }
             if (connection.State == ConnectionState.Open)
                 connection.Close();

             if (executed == 1)
                 return true;
             else
                 return false;
        }


        public List<string> addPlayerToGame(GameStart aux, bool challenge,bool newAlgo)
        {
            //balancear
            List<Tuple<double, string>> players = new List<Tuple<double, string>>();
            string query;
            DataSet algo;
            if (connection.State == ConnectionState.Closed)
                connection.Open();
            MySqlCommand cmd;
            foreach (string x in aux)
            {
                query = "(select * from Vouch where Nombre = '" + x + "')";
                adapter = new MySqlDataAdapter(query, connection);
                algo = new DataSet();
                int s = adapter.Fill(algo);
                if (s == 0)
                    return null;
                else
                {
                    players.Add(new Tuple<double, string>((double)algo.Tables[0].Rows[0]["Exp"], x));
                }
            }
            if (!challenge)
            {
                players.Sort(ComparePlayers);
                if(newAlgo)
                    players = balance(players); 
            }
            for (int i = 0; i < CoreMaster.maxPlayer; i++)
            {
                if (!challenge)
                {
                    if (!newAlgo)
                    {
                        if (i % 2 == 0)
                        {
                            string insertSQL = "INSERT INTO vouch_plays_game (Vouch_idvouch, Game_idGame,team) VALUES ((select idvouch from vouch where Nombre = '" + players[i].Item2 + "'),'" + aux.GetGame().idGame + "',1)";
                            cmd = new MySqlCommand(insertSQL, connection);
                            cmd.ExecuteNonQuery();
                        }
                        else
                        {
                            string insertSQL = "INSERT INTO vouch_plays_game (Vouch_idvouch, Game_idGame,team) VALUES ((select idvouch from vouch where Nombre = '" + players[i].Item2 + "'),'" + aux.GetGame().idGame + "',2)";
                            cmd = new MySqlCommand(insertSQL, connection);
                            cmd.ExecuteNonQuery();
                        }
                    }
                    else
                    {
                        if (i < 5)
                        {
                            string insertSQL = "INSERT INTO vouch_plays_game (Vouch_idvouch, Game_idGame,team) VALUES ((select idvouch from vouch where Nombre = '" + players[i].Item2 + "'),'" + aux.GetGame().idGame + "',1)";
                            cmd = new MySqlCommand(insertSQL, connection);
                            cmd.ExecuteNonQuery();
                        }
                        else
                        {
                            string insertSQL = "INSERT INTO vouch_plays_game (Vouch_idvouch, Game_idGame,team) VALUES ((select idvouch from vouch where Nombre = '" + players[i].Item2 + "'),'" + aux.GetGame().idGame + "',2)";
                            cmd = new MySqlCommand(insertSQL, connection);
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
                else
                {
                    if (i < 5)
                    {
                        string insertSQL = "INSERT INTO vouch_plays_game (Vouch_idvouch, Game_idGame,team) VALUES ((select idvouch from vouch where Nombre = '" + players[i].Item2 + "'),'" + aux.GetGame().idGame + "',1)";
                        cmd = new MySqlCommand(insertSQL, connection);
                        cmd.ExecuteNonQuery();
                    }
                    else
                    {
                        string insertSQL = "INSERT INTO vouch_plays_game (Vouch_idvouch, Game_idGame,team) VALUES ((select idvouch from vouch where Nombre = '" + players[i].Item2 + "'),'" + aux.GetGame().idGame + "',2)";
                        cmd = new MySqlCommand(insertSQL, connection);
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            if (connection.State == ConnectionState.Open)
                connection.Close();
            List<string> queryQ = new List<string>();
            if (!challenge)
            {
                if (!newAlgo)
                {
                    queryQ = (from pp in players
                              where players.IndexOf(pp) % 2 == 0
                              select pp.Item2).ToList();
                    var queryQ2 = (from pp in players
                                   where players.IndexOf(pp) % 2 != 0
                                   select pp.Item2).ToList();
                    queryQ.AddRange(queryQ2);
                }
                else
                {
                    queryQ = (from pp in players
                              select pp.Item2).ToList();
                }
            }
            else
            {
                queryQ = (from pp in players
                          select pp.Item2).ToList();
            }
            return queryQ;
        }
        struct SSTema
        {
            public int p1, p2, p3, p4, p5;
            public double total;
        }
        private List<Tuple<double, string>> balance(List<Tuple<double, string>> players)
        {
            var jugadores = players.Select(x => x.Item1).ToList();
            List<SSTema> garrCombinaciones = new List<SSTema>();
            int nA, nB, nC, nD, nE;
            nA = nB = nC = nD = nE = 0;
            SSTema dat;

            while (nA < 10)
            {
                nB = nA + 1;
                while (nB < 10)
                {
                    nC = nB + 1;

                    while (nC < 10)
                    {
                        nD = nC + 1;
                        while (nD < 10)
                        {
                            nE = nD + 1;
                            while (nE < 10)
                            {
                                dat.p1 = nA;
                                dat.p2 = nB;
                                dat.p3 = nC;
                                dat.p4 = nD;
                                dat.p5 = nE;
                                dat.total = jugadores[nA] + jugadores[nB] + jugadores[nC] + jugadores[nD] + jugadores[nE];
                                garrCombinaciones.Add(dat);

                                nE++;
                            }
                            nD++;
                        }
                        nC++;
                    }
                    nB++;
                }
                nA++;
            }

            double nIdeal = players.Select(x => x.Item1).ToList().Sum() / 2;
            int nMejor = 1;
            double nDiferencia = 99999;
            double nAux = 0;
            int nSize = garrCombinaciones.Count;

            for (int i = 0; i < nSize; i++)
            {
                nAux = Math.Abs(garrCombinaciones[i].total - nIdeal);
                if (nAux < nDiferencia)
                {
                    nDiferencia = nAux;
                    nMejor = i;
                }
            }
            List<int> mejores = new List<int>();
            mejores.Add(garrCombinaciones[nMejor].p1);
            mejores.Add(garrCombinaciones[nMejor].p2);
            mejores.Add(garrCombinaciones[nMejor].p3);
            mejores.Add(garrCombinaciones[nMejor].p4);
            mejores.Add(garrCombinaciones[nMejor].p5);
            List<Tuple<double, string>> Equipo1 = new List<Tuple<double, string>>();
            List<Tuple<double, string>> Equipo2 = new List<Tuple<double, string>>();
            int f = 0;
            for (int i = 0; i < 10; i++)
            {

                if ((f < 5) && (mejores[f] == i))
                {
                    Equipo1.Add(new Tuple<double, string>(players[i].Item1, players[i].Item2));
                    f++;
                }
                else
                {
                    Equipo2.Add(new Tuple<double, string>(players[i].Item1, players[i].Item2));
                }
            }
            Equipo1.Sort(ComparePlayers);
            Equipo2.Sort(ComparePlayers);
            Equipo1.AddRange(Equipo2);
            return Equipo1;
        }
        private static int ComparePlayers(Tuple<double, string> x, Tuple<double, string> y)
        {
            return y.Item1.CompareTo(x.Item1);
        }
        public string Stats(string User)
        {
            User = User.ToLower();
            var us = User;
            User = "\"" + User + "\"";
            string query = "(select * from Vouch where Inactivo='0' AND Nombre = " + User + ")";
            //string query2 = "SELECT * FROM Vouch_plays_Game JOIN Game ON Game_idGame = idGame WHERE Vouch_idvouch =" + query;
            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query, connection);
            DataSet algo = new DataSet();
            int i = adapter.Fill(algo);

            if (i == 0)
                return null;
            else
                return "[" + us + "] Puntos: " + Math.Round((double)algo.Tables[0].Rows[0]["Exp"], 2) + " Streak: " + algo.Tables[0].Rows[0]["Streak"];
        }
        public double puntos(string User)
        {
            User = User.ToLower();
            var us = User;
            User = "\"" + User + "\"";
            string query = "(select * from Vouch where Inactivo='0' AND Nombre = " + User + ")";
            //string query2 = "SELECT * FROM Vouch_plays_Game JOIN Game ON Game_idGame = idGame WHERE Vouch_idvouch =" + query;
            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query, connection);
            DataSet algo = new DataSet();
            int i = adapter.Fill(algo);

            if (i == 0)
                return 0;
            else
                return (double)algo.Tables[0].Rows[0]["Exp"];
        }

        public string Top()
        {
            string query = "SELECT Nombre,Exp FROM Vouch where Inactivo='0' ORDER BY Exp DESC LIMIT 10 ";
            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query, connection);
            DataSet algo = new DataSet();
            adapter.Fill(algo);
            string  ranking = "";
            int i = 0;
            int cant= algo.Tables[0].Rows.Count;
            foreach (DataRow x in algo.Tables[0].Rows)
            {
                if (i == cant - 1)
                    ranking = ranking + (i + 1) + "." + x[0].ToString() + "[" + Math.Round((double)x[1]) + "]";
                else
                    ranking = ranking + (i + 1) + "." + x[0].ToString() + "[" + Math.Round((double)x[1]) + "] ";
                i++;
            }
            return ranking;
        }

        public bool unVouch(string UnVouched,int Admin)
        {
            string formatForMySql = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string insertSQL = "UPDATE vouch SET Fecha='"+formatForMySql+"',Inactivo ='1', AdminUV="+Admin+" WHERE Nombre= '" + UnVouched + "'";
            MySqlCommand cmd = new MySqlCommand(insertSQL, connection);
            int executed = 0;
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            executed = cmd.ExecuteNonQuery();
            if(connection.State == ConnectionState.Open)
            {             
                connection.Close();
            }
            if (executed == 1)
                return true;
            else
                return false;
        }

        internal string NoobRank()
        {
            string query2 = "SELECT count(*) FROM Vouch where Inactivo='0'";
            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query2, connection);
            DataSet algo2 = new DataSet();
            adapter.Fill(algo2);
            int cant2 = (int)(long)algo2.Tables[0].Rows[0][0];
            string query = "SELECT Nombre,Exp FROM Vouch where Inactivo='0' ORDER BY Exp ASC LIMIT 10 ";
            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query, connection);
            DataSet algo = new DataSet();
            adapter.Fill(algo);
            string ranking = "";
            int i = 0;
            int cant = algo.Tables[0].Rows.Count;
            foreach (DataRow x in algo.Tables[0].Rows)
            {
                if (i == cant - 1)
                    ranking = ranking + (cant2 - i + 1) + "." + x[0].ToString() + "[" + Math.Round((double)x[1]) + "]";
                else
                    ranking = ranking + (cant2 - i + 1) + "." + x[0].ToString() + "[" + Math.Round((double)x[1]) + "] ";

                i++;

            }
            return ranking;
        }

        internal List<Bans> GetBans()
        {
            string query = "SELECT * FROM bans where Expirado ='0' ORDER BY FechaExpiracion ASC";
            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query, connection);
            List<Bans> lista= new List<Bans>();
            DataSet algo = new DataSet();
            adapter.Fill(algo);
            int cant = algo.Tables[0].Rows.Count;
            foreach (DataRow x in algo.Tables[0].Rows)
            {
                lista.Add(new Bans((int)x[0], x[1].ToString(), DateTime.Parse(x[2].ToString()), new Admin((int)x[3], "", 0)));
            }
            return lista;
        }
        internal Bans GetBan(string User)
        {
            string query = "SELECT * FROM bans where Expirado ='0' AND Nombre = '" + User + "'";
            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query, connection);
            Bans baneado = null;
            DataSet algo = new DataSet();
            int cant= adapter.Fill(algo);
            if (cant != 0)
            {
                var x = algo.Tables[0].Rows[0];
                baneado = new Bans((int)x[0], x[1].ToString(), DateTime.Parse(x["FechaExpiracion"].ToString()), new Admin((int)x[3], "", 0));

            }
            return baneado;
        }

        internal bool DeleteBan(int p)
        {
            string insertSQL = "UPDATE bans SET Expirado ='1' WHERE idBans = '" + p + "'";
            MySqlCommand cmd = new MySqlCommand(insertSQL, connection);
            int executed = 0;
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
                executed = cmd.ExecuteNonQuery();
                connection.Close();

            }
            if (executed == 1)
                return true;
            else
                return false;
        }

        internal bool Ban(string User,int idAdmin)
        {
            string insertSQL = "INSERT INTO bans (Nombre, FechaExpiracion, Admin_idAdmin) VALUES ('" + User + "', '" + (DateTime.Now.AddDays(1800)).ToString("yyyy-MM-dd HH:mm") + "', '" + idAdmin + "')";
            MySqlCommand cmd = new MySqlCommand(insertSQL, connection);
            int executed = 0;
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
                executed = cmd.ExecuteNonQuery();
                connection.Close();
            }
            if (executed == 1)
                return true;
            else
                return false;
        }
        internal bool Ban(string User, int idAdmin,int days,string motivo)
        {
            string insertSQL = "INSERT INTO bans (Nombre, FechaExpiracion, Admin_idAdmin,Motivo) VALUES ('" + User + "', '" + (DateTime.Now.AddDays(days)).ToString("yyyy-MM-dd HH:mm") + "', '" + idAdmin + "','" + motivo + "')";
            MySqlCommand cmd = new MySqlCommand(insertSQL, connection);

            int executed = 0;
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
                executed = cmd.ExecuteNonQuery();
                connection.Close();

            }
            if (executed == 1)
                return true;
            else
                return false;
        }

        internal int getAdminId(string user)
        {
            user = user.ToLower();
            user = "\"" + user + "\"";
            string query = "select idAdmin from Admin where Nombre = " + user;
            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query, connection);
            DataSet algo = new DataSet();
            if (adapter.Fill(algo) == 1)
                return (int)algo.Tables[0].Rows[0][0];
            else
                return -1;
        }

        internal string Me(string User)
        {
            /*
             * SELECT vouch.Nombre,Exp,vouch.Streak,Confianza,Traunt,admin.Nombre FROM  vouch
             * JOIN admin ON Voucher=idAdmin;
             * //principal
             * SELECT count(*) FROM vouch
             * JOIN vouch_plays_game ON Vouch_idvouch=idvouch
             * where idvouch='196';
             * //cantidad jugadas
             * SELECT count(*) FROM vouch
             * JOIN vouch_plays_game ON Vouch_idvouch=idvouch
             * where idvouch='196' and Result > 0;
             * //cantidad de ganadas
             * SELECT count(*) FROM vouch
             * JOIN vouch_plays_game ON Vouch_idvouch=idvouch
             * where idvouch='196' and Result < 0;
             * //cantidad de perdidas
             * 
             */
            User = User.ToLower();
            var us = User;
            User = "\"" + User + "\"";
            int ganadas, perdidas, jugadas;
            ganadas = perdidas = jugadas = 0;
            string query = "(SELECT vouch.Nombre,Exp,vouch.Streak,Confianza,Traunt,admin.Nombre,Link FROM  vouch JOIN admin ON Voucher=idAdmin WHERE vouch.Inactivo=0 AND vouch.Nombre = " + User + ")";
            string query2;

            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query, connection);
            DataSet algo = new DataSet();
            int i = adapter.Fill(algo);
            DataSet algo2;
            query2 = "SELECT count(*) FROM vouch JOIN vouch_plays_game ON Vouch_idvouch=idvouch where idvouch=" + "(select idvouch from Vouch where Inactivo=0 AND Nombre = " + User + ")";
            adapter = new MySqlDataAdapter(query2, connection);
            algo2 = new DataSet();
            i = adapter.Fill(algo2);
            if (i != 0)
                jugadas = int.Parse(algo2.Tables[0].Rows[0][0].ToString());
            query2 = "SELECT count(*) FROM vouch JOIN vouch_plays_game ON Vouch_idvouch=idvouch where idvouch=" + "(select idvouch from Vouch where Inactivo=0 AND Nombre = " + User + ")" + " and Result > 0";
            adapter = new MySqlDataAdapter(query2, connection);
            algo2 = new DataSet();
            i = adapter.Fill(algo2);
            if (i != 0)
                ganadas = int.Parse(algo2.Tables[0].Rows[0][0].ToString());
            query2 = "SELECT count(*) FROM vouch JOIN vouch_plays_game ON Vouch_idvouch=idvouch where idvouch=" + "(select idvouch from Vouch where Nombre = " + User + ")" + " and Result < 0";
            adapter = new MySqlDataAdapter(query2, connection);
            algo2 = new DataSet();
            i = adapter.Fill(algo2);
            if (i != 0)
                perdidas = int.Parse(algo2.Tables[0].Rows[0][0].ToString());

            if (i == 0)
                return null;
            else
                //return "[" + us + "] Puntos: " + Math.Round((double)algo.Tables[0].Rows[0]["Exp"], 2) + " Streak: " + algo.Tables[0].Rows[0]["Streak"] + " Confianza: " + Math.Round((double)algo.Tables[0].Rows[0]["Confianza"], 2) +
                //      " Truants: " + algo.Tables[0].Rows[0]["Traunt"];
                return "[" + us + "] EXP: " + Math.Round((double)algo.Tables[0].Rows[0]["Exp"]) + " Streak: " + algo.Tables[0].Rows[0]["Streak"] + " Confianza: " + Math.Round((double)algo.Tables[0].Rows[0]["Confianza"], 2)
                     +  " Ganadas: " + ganadas + " Perdidas: " + perdidas + " Jugadas: " + jugadas + " Voucher: " + algo.Tables[0].Rows[0]["Nombre1"]+" Razon: "+ algo.Tables[0].Rows[0]["Link"];

        }



        internal List<GameStart> GetUnfinishedGames()
        {
            string query = "Select * from game where Resultado is null";
            adapter = new MySqlDataAdapter(query, connection);
            DataSet algo = new DataSet();
            int i = adapter.Fill(algo);
            List<GameStart> Lista= new List<GameStart>();
            if (i == 0)
                return null;
            else
            {
                foreach (DataRow x in algo.Tables[0].Rows)
                {
                    GameStart GS = new GameStart();
                    GS.GetGame().idGame = (int)x["idGame"];
                    GS.GetGame().Fecha = DateTime.Parse(x["Fecha"].ToString());
                    string queryinternal = "SELECT * FROM vouch_plays_game JOIN vouch ON idvouch = Vouch_idvouch where Game_idGame='" + x["idGame"] + "'";
                    adapter = new MySqlDataAdapter(queryinternal, connection);
                    DataSet algointernal = new DataSet();
                    int ii = adapter.Fill(algointernal);
                    foreach (DataRow f in algointernal.Tables[0].Rows)
                    {
                        if((int)f["team"]==1)
                        GS.AddPlayer(f["Nombre"].ToString());
                    }
                    foreach (DataRow f in algointernal.Tables[0].Rows)
                    {
                        if ((int)f["team"] == 2)
                            GS.AddPlayer(f["Nombre"].ToString());
                    }
                    Lista.Add(GS);
                }
            }
            return Lista;
        }


        public void Dispose()
        {
            if(DBconnection.Instance.connection.State== ConnectionState.Open)
                DBconnection.Instance.connection.Close(); 
        }
        internal GameStart ExistGame(int id,resultado res = resultado.none)
        {
            string query = "Select * from game where idGame = '" + id + "'";
            adapter = new MySqlDataAdapter(query, connection);
            DataSet algo = new DataSet();
            int i = adapter.Fill(algo);
            GameStart GS = new GameStart();
            List<GameStart> Lista = new List<GameStart>();
            if (i == 0)
                return null;
            else
            {
                foreach (DataRow x in algo.Tables[0].Rows)
                {
                    if (x["Resultado"].ToString() == "")
                        GS.GetGame()._resultado = resultado.none;
                    else
                        GS.GetGame()._resultado = (resultado)Enum.Parse(typeof(resultado), x["Resultado"].ToString());
                    GS.GetGame().idGame = (int)x["idGame"];
                    GS.GetGame().Fecha = DateTime.Parse(x["Fecha"].ToString());
                    string queryinternal = "SELECT * FROM vouch_plays_game JOIN vouch ON idvouch = Vouch_idvouch where Game_idGame='" + x["idGame"] + "'";
                    adapter = new MySqlDataAdapter(queryinternal, connection);
                    DataSet algointernal = new DataSet();
                    int ii = adapter.Fill(algointernal);
                    foreach (DataRow f in algointernal.Tables[0].Rows)
                    {
                        if (f["Streak"].ToString() == "")
                            GS.Streak.Add((int)f["Streak1"]);
                        else
                            GS.Streak.Add((int)f["Streak"]);
                        if (f["Result"].ToString() != "")
                            GS.Diffs.Add((double)f["Result"]);
                        GS.GetGame().Fecha = DateTime.Parse(x["Fecha"].ToString());
                        GS.AddPlayer(f["Nombre"].ToString());
                        GS.Teams.Add((int)f["team"]);
                    }
                    List<string> players=new List<string>();
                    foreach (var pla in GS._Players)
                    {
                        players.Add(pla);
                    }
                    GS._Players.Clear();
                    int it=0;
                    List<int> fix = new List<int>();
                    foreach (var pla in players)
                    {
                        if (GS.Teams[it] == 1)
                        {
                            GS._Players.Add(pla);
                            fix.Add(it);
                        } it++;
                    }
                    it = 0;
                    foreach (var pla in players)
                    {
                        if (GS.Teams[it] == 2)
                        {
                            GS._Players.Add(pla);
                            fix.Add(it);
                        } it++;
                    }
                    List<int> streak = new List<int>();
                    foreach (var pla in GS.Streak)
                    {
                        streak.Add(pla);
                    }
                    GS.Streak.Clear();
                    List<int> teams = new List<int>();
                    foreach (var pla in GS.Teams)
                    {
                        teams.Add(pla);
                    }
                    GS.Teams.Clear();
                    List<double> diff = new List<double>();
                    foreach (var pla in GS.Diffs)
                    {
                        diff.Add(pla);
                    }
                    
                    GS.Diffs.Clear();
                    foreach (var fi in fix)
                    {
                       
                        GS.Streak.Add(streak[fi]);
                        GS.Teams.Add(teams[fi]);
                        if(diff.Count!=0)
                            GS.Diffs.Add(diff[fi]); 
                    }
                }
                return GS;
            }
        }

      
        internal void RestorePoints(GameStart GS)
        {
            MySqlCommand cmd;

            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            int result = -1;
            if (GS.GetGame()._resultado == resultado.sentinel)
            {
                result = 1;
            }
            else if (GS.GetGame()._resultado == resultado.scourge)
            {
                result = 2;
            }
            else if (GS.GetGame()._resultado == resultado.draw)
            {
                return;
            }
            for (int kk = 0; kk < GS._Players.Count; kk++)
            {
                string insertSQL2;
                if (GS.Teams[kk] == result)
                {
                    insertSQL2 = "UPDATE vouch SET Confianza = Confianza - '0.3' , Streak = Streak - 1 , Exp = Exp - '" + GS.Diffs[kk] + "'  WHERE Nombre ='" + GS._Players[kk] + "'";
                }
                else
                {
                    insertSQL2 = "UPDATE vouch SET Confianza = Confianza - '0.3' , Streak = '" + GS.Streak[kk] + "' , Exp = Exp - '" + GS.Diffs[kk] + "'  WHERE Nombre ='" + GS._Players[kk] + "'";
                }
                cmd = new MySqlCommand(insertSQL2, connection);
                cmd.ExecuteNonQuery();
            }
            if (connection.State == ConnectionState.Open)
            {
                connection.Close();
            }
        }

        internal string GetRango(string User)
        {
            User = User.ToLower();
            var us = User;
            User = "\"" + User + "\"";
            string query = "(select * from admin where Nombre = " + User + ")";
            //string query2 = "SELECT * FROM Vouch_plays_Game JOIN Game ON Game_idGame = idGame WHERE Vouch_idvouch =" + query;
            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query, connection);
            DataSet algo = new DataSet();
            int i = adapter.Fill(algo);

            if (i == 0)
                return null;
            else
            {
                switch ((int)algo.Tables[0].Rows[0]["Access"])
                {
                    case 0:
                        return "Head Admin";
                    case 1:
                        return "Manager";
                    case 2:
                        return "Voucher";
                    case 3:
                        return "Capitan";
                    case 4:
                        return "Host";
                    default:
                        return null;
                }

            }
        }
        internal int? GetRango2(string User)
        {
            User = User.ToLower();
            var us = User;
            User = "\"" + User + "\"";
            string query = "(select * from admin where Nombre = " + User + ")";
            //string query2 = "SELECT * FROM Vouch_plays_Game JOIN Game ON Game_idGame = idGame WHERE Vouch_idvouch =" + query;
            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query, connection);
            DataSet algo = new DataSet();
            int i = adapter.Fill(algo);

            if (i == 0)
                return null;
            else
            {
                return (int)algo.Tables[0].Rows[0]["Access"];
            }
        }

        internal bool haveTruant(GameStart GS)
        {
            throw new NotImplementedException();
        }

        internal void DeleteTraunt(GameStart GS)
        {
            throw new NotImplementedException();
        }

        internal bool haveExclude(GameStart GS, string player)
        {
            throw new NotImplementedException();
        }

        internal void DeleteExclude(GameStart GS, string player)
        {
            throw new NotImplementedException();
        }


        internal string Baninfo(string Payload)
        {
            Payload = Payload.ToLower();
            var us = Payload;
            Payload = "\"" + Payload + "\"";
            string query = "(SELECT  FechaExpiracion as GG,admin.Nombre as ADM,Expirado,Motivo FROM bans JOIN admin ON Admin_idAdmin=idAdmin WHERE bans.Nombre = " + Payload + " AND Expirado='0')";
            string query2 = "SELECT COUNT(*) FROM bans WHERE Nombre =" + Payload;
            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query, connection);
            DataSet algo = new DataSet();
            int i = adapter.Fill(algo);
            string BanInfo = "";
            if (i > 0)
            {
                DataRow f = algo.Tables[0].Rows[0];
                if (f["Expirado"].ToString() != "True")
                {
                    var dt = DateTime.Parse(f["GG"].ToString()) - DateTime.Now;

                    BanInfo = BanInfo + " Baneado por : " + f["ADM"] + " Vence en : " + dt.Days.ToString() + " dias " + dt.Hours + "hs " + dt.Minutes + "min " + " Motivo: " + f["Motivo"].ToString();
                }
                else
                {
                    BanInfo = "No tiene bans activos";
                }
            }
            else
            {
                return null;
            }
            adapter = new MySqlDataAdapter(query2, connection);
            DataSet algo2 = new DataSet();
            i = adapter.Fill(algo2);
            DataRow g = algo2.Tables[0].Rows[0];
            if (i > 0)
            {
                BanInfo = BanInfo + " Cant: " + g[0].ToString();
            }
            return BanInfo;
        }

        internal string Rank(string User)
        {
            string query = "SELECT Nombre,Exp FROM Vouch where Inactivo=0 ORDER BY Exp DESC LIMIT 1200";
           
            adapter = new MySqlDataAdapter(query, connection);
            DataSet algo = new DataSet();
            adapter.Fill(algo);
            int cant= algo.Tables[0].Rows.Count;
            int i = 1;
            double exp;
            string lab;
            foreach (DataRow x in algo.Tables[0].Rows)
            {
                if (x[0].ToString().ToLower() == User)
                {
                    exp = (double)x[1];
                    if (exp <      800) { lab = "[Pt]"; }
                    else if (exp < 900) { lab = "[+100 Gold creep]"; }
                    else if (exp < 1000) { lab = "[Novato]"; }
                    else if (exp < 1100) { lab = "[Pubber]"; }
                    else if (exp < 1200) { lab = "[Intermedio]"; }
                    else if (exp < 1300) { lab = "[Habilidoso]"; }
                    else if (exp < 1400) { lab = "[Profesional]"; }
                    else if (exp < 1500) { lab = "[Invencible ]"; }
                    else if (exp < 1600) { lab = "[Re-Imba]"; }
                    else { lab = "[Godlike]"; }

                    return lab + i + "/" + cant;
                }
                i++;
            }

            return "";
            
        }


        public int Warn(string User, int idAdmin, int hp, string motivo)
        {
            int HP = CurrentHP(User);
            if (HP + hp > 100)
                return 100;

            string formatForMySql = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string insertSQL = "INSERT INTO warns (Nombre, HP,idAdmin,Link,Fecha) VALUES ('" + User + "', " + hp + ", '" + idAdmin + "','" + motivo + "','" + formatForMySql + "')";
            MySqlCommand cmd = new MySqlCommand(insertSQL, connection);

            int executed = 0;
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
                executed = cmd.ExecuteNonQuery();
                connection.Close();

            }
            if (executed == 1)
                return HP+hp;
            else
                return -1;
        }

        internal int CurrentHP(string User)
        {
            string query2 = "(select * from warns where Nombre = '" + User + "')";
            //string query2 = "SELECT * FROM Vouch_plays_Game JOIN Game ON Game_idGame = idGame WHERE Vouch_idvouch =" + query;
            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query2, connection);
            DataSet algo2 = new DataSet();
            int i = adapter.Fill(algo2);
            int HP = 100;
            foreach (DataRow x in algo2.Tables[0].Rows)
            {
                HP += int.Parse(x["Hp"].ToString());
            }
            return HP;
        }

        internal string Hp(string Usuario)
        {
            string query = "(select * from warns where Nombre = '" + Usuario + "')";
            //string query2 = "SELECT * FROM Vouch_plays_Game JOIN Game ON Game_idGame = idGame WHERE Vouch_idvouch =" + query;
            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query, connection);
            DataSet algo = new DataSet();
            int i = adapter.Fill(algo);
            int HP=100;
            foreach (DataRow x in algo.Tables[0].Rows)
            {
                HP += int.Parse(x["Hp"].ToString());
            }
            return "Hp Actual: " + HP;
        }

        internal int Muertes(string User)
        {
            string query = "(select * from vouch where Nombre = '" + User + "')";
            //string query2 = "SELECT * FROM Vouch_plays_Game JOIN Game ON Game_idGame = idGame WHERE Vouch_idvouch =" + query;
            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query, connection);
            DataSet algo = new DataSet();
            int i = adapter.Fill(algo);
            int HP = 0;
            foreach (DataRow x in algo.Tables[0].Rows)
            {
                HP += int.Parse(x["Muertes"].ToString());
            }
            return HP; 
        }
        internal string menosherido()
        {
            string query = "SELECT Nombre,Sum(HP)+100 as HP FROM warns group by Nombre Having HP <100 order by HP desc LIMIT 10;";
            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query, connection);
            DataSet algo = new DataSet();
            adapter.Fill(algo);
            string ranking = "";
            int i = 0;
            int cant = algo.Tables[0].Rows.Count;
            foreach (DataRow x in algo.Tables[0].Rows)
            {
                if (i == cant - 1)
                    ranking = ranking + (i + 1) + "." + x[0].ToString() + "[" + Math.Round((double)x[1]) + "]";
                else
                    ranking = ranking + (i + 1) + "." + x[0].ToString() + "[" + Math.Round((double)x[1]) + "] ";
                i++;
            }
            return ranking;
        }

        internal string masherido()
        {
            string query = "SELECT Nombre,Sum(HP)+100 as HP FROM  warns group by Nombre Having HP <100 order by HP asc LIMIT 10;";
            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query, connection);
            DataSet algo = new DataSet();
            adapter.Fill(algo);
            string ranking = "";
            int i = 0;
            int cant = algo.Tables[0].Rows.Count;
            foreach (DataRow x in algo.Tables[0].Rows)
            {
                if (i == cant - 1)
                    ranking = ranking + (i + 1) + "." + x[0].ToString() + "[" + Math.Round((double)x[1]) + "]";
                else
                    ranking = ranking + (i + 1) + "." + x[0].ToString() + "[" + Math.Round((double)x[1]) + "] ";
                i++;
            }
            return ranking;
        }


        internal bool Matar(string user)
        {
            string insertSQL = "UPDATE Vouch SET MUERTES=MUERTES+1'" + user + "')";
            MySqlCommand cmd = new MySqlCommand(insertSQL, connection);
            int executed = 0;
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
                executed = cmd.ExecuteNonQuery();
                connection.Close();
            }
            if (executed == 1)
                return true;
            else
                return false;
        }

        internal IEnumerable<string> WarnHPrestore()
        {
            string query = "Select SUM(HP) from warns group by Nombre";
            //prepare adapter to run query
            adapter = new MySqlDataAdapter(query, connection);
            DataSet algo = new DataSet();
            adapter.Fill(algo);
            List<string> ppl=new List<string>();
            foreach (DataRow x in algo.Tables[0].Rows)
            {
                ppl.Add(x[0].ToString());
            }
            return ppl;
        }
    }
    public static class MyExtensions
    {
        public static void Shuffle<T>(this IList<T> list)
        {
            RNGCryptoServiceProvider provider = new RNGCryptoServiceProvider();
            int n = list.Count;
            while (n > 1)
            {
                byte[] box = new byte[1];
                do provider.GetBytes(box);
                while (!(box[0] < n * (Byte.MaxValue / n)));
                int k = (box[0] % n);
                n--;
                T value = list[k];
                list[k] = list[n];
                list[n] = value;
            }
        }
    }
}
